# coding: utf-8
'''
@autor1: João Batista nº5479
@autor2: Luis Cabaço nº5606
@data Inicio: 24/10/2012 - 16:47
@data Fim: 04/11/2012 - 17:20
@obs:

Módulo para criar a bd
@fonte: http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-step-sqlalchemy-tutorial-part-1-of-2/
@fonte: http://www.blog.pythonlibrary.org/2010/02/03/another-step-by-step-sqlalchemy-tutorial-part-2-of-2/
'''

# import dos modulos do SQLAlchemy e XLRD
# SQLAlchemy - Ligaçao do Python com SQL
# XLRD - Permite a leitura e tratamento de ficheiros XLS
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from xlrd import open_workbook

from controladorv2 import memoria_partilhada

Base = declarative_base() 

class Registo(Base):
	'''
	Classe Registo
		- Esta classe vai ser usada como objecto para 
		fazer o preenchimento da Base de dados.
		- Cada objecto corresponde a um registo.
	'''

	__tablename__ = 'students_table'
	
	id = Column(Integer, primary_key = True)
	estabelecimento = Column(String)
	unidade_organica = Column(String)
	nivel_de_formacao = Column(String)
	curso_superior = Column(String)
	area_de_formacao = Column(String)
	Ano_95_96_H = Column(Integer)
	Ano_95_96_M = Column(Integer)
	Ano_95_96_T = Column(Integer)
	Ano_96_97_H = Column(Integer)
	Ano_96_97_M = Column(Integer)
	Ano_96_97_T = Column(Integer)
	Ano_97_98_H = Column(Integer)
	Ano_97_98_M = Column(Integer)
	Ano_97_98_T = Column(Integer)
	Ano_98_99_H = Column(Integer)
	Ano_98_99_M = Column(Integer)
	Ano_98_99_T = Column(Integer)
	Ano_99_00_H = Column(Integer)
	Ano_99_00_M = Column(Integer)
	Ano_99_00_T = Column(Integer)
	Ano_00_01_H = Column(Integer)
	Ano_00_01_M = Column(Integer)
	Ano_00_01_T = Column(Integer)
	Ano_01_02_H = Column(Integer)
	Ano_01_02_M = Column(Integer)
	Ano_01_02_T = Column(Integer)
	Ano_02_03_H = Column(Integer)
	Ano_02_03_M = Column(Integer)
	Ano_02_03_T = Column(Integer)
	Ano_03_04_H = Column(Integer)
	Ano_03_04_M = Column(Integer)
	Ano_03_04_T = Column(Integer)
	Ano_04_05_H = Column(Integer)
	Ano_04_05_M = Column(Integer)
	Ano_04_05_T = Column(Integer)
	Ano_05_06_H = Column(Integer)
	Ano_05_06_M = Column(Integer)
	Ano_05_06_T = Column(Integer)
	Ano_06_07_H = Column(Integer)
	Ano_06_07_M = Column(Integer)
	Ano_06_07_T = Column(Integer)
	Ano_07_08_H = Column(Integer)
	Ano_07_08_M = Column(Integer)
	Ano_07_08_T = Column(Integer)
	Ano_08_09_H = Column(Integer)
	Ano_08_09_M = Column(Integer)
	Ano_08_09_T = Column(Integer)
	Ano_09_10_H = Column(Integer)
	Ano_09_10_M = Column(Integer)
	Ano_09_10_T = Column(Integer)
	Ano_10_11_H = Column(Integer)
	Ano_10_11_M = Column(Integer)
	Ano_10_11_T = Column(Integer)
	
	def __init__(self, estabelecimento, unidade_organica,
				 nivel_de_formacao, curso_superior, area_de_formacao,
				 Ano_95_96_H, Ano_95_96_M, Ano_95_96_T, Ano_96_97_H,
				 Ano_96_97_M, Ano_96_97_T, Ano_97_98_H, Ano_97_98_M,
				 Ano_97_98_T, Ano_98_99_H, Ano_98_99_M, Ano_98_99_T,
				 Ano_99_00_H, Ano_99_00_M, Ano_99_00_T, Ano_00_01_H,
				 Ano_00_01_M, Ano_00_01_T, Ano_01_02_H, Ano_01_02_M,
				 Ano_01_02_T, Ano_02_03_H, Ano_02_03_M, Ano_02_03_T,
				 Ano_03_04_H, Ano_03_04_M, Ano_03_04_T, Ano_04_05_H,
				 Ano_04_05_M, Ano_04_05_T, Ano_05_06_H, Ano_05_06_M,
				 Ano_05_06_T, Ano_06_07_H, Ano_06_07_M, Ano_06_07_T,
				 Ano_07_08_H, Ano_07_08_M, Ano_07_08_T, Ano_08_09_H,
				 Ano_08_09_M, Ano_08_09_T, Ano_09_10_H, Ano_09_10_M,
				 Ano_09_10_T, Ano_10_11_H, Ano_10_11_M, Ano_10_11_T):
		'''
		Construtor da classe Registo
			- Os atributos deverão ser inicializados aqui
		'''
		
		self.estabelecimento = estabelecimento
		self.unidade_organica = unidade_organica
		self.nivel_de_formacao = nivel_de_formacao
		self.curso_superior = curso_superior
		self.area_de_formacao = area_de_formacao
		self.Ano_95_96_H = Ano_95_96_H
		self.Ano_95_96_M = Ano_95_96_M
		self.Ano_95_96_T = Ano_95_96_T
		self.Ano_96_97_H = Ano_96_97_H
		self.Ano_96_97_M = Ano_96_97_M
		self.Ano_96_97_T = Ano_96_97_T
		self.Ano_97_98_H = Ano_97_98_H
		self.Ano_97_98_M = Ano_97_98_M
		self.Ano_97_98_T = Ano_97_98_T
		self.Ano_98_99_H = Ano_98_99_H
		self.Ano_98_99_M = Ano_98_99_M 
		self.Ano_98_99_T = Ano_98_99_T
		self.Ano_99_00_H = Ano_99_00_H
		self.Ano_99_00_M = Ano_99_00_M
		self.Ano_99_00_T = Ano_99_00_T
		self.Ano_00_01_H = Ano_00_01_H
		self.Ano_00_01_M = Ano_00_01_M
		self.Ano_00_01_T = Ano_00_01_T
		self.Ano_01_02_H = Ano_01_02_H
		self.Ano_01_02_M = Ano_01_02_M
		self.Ano_01_02_T = Ano_01_02_T
		self.Ano_02_03_H = Ano_02_03_H
		self.Ano_02_03_M = Ano_02_03_M
		self.Ano_02_03_T = Ano_02_03_T
		self.Ano_03_04_H = Ano_03_04_H
		self.Ano_03_04_M = Ano_03_04_M
		self.Ano_03_04_T = Ano_03_04_T
		self.Ano_04_05_H = Ano_04_05_H
		self.Ano_04_05_M = Ano_04_05_M
		self.Ano_04_05_T = Ano_04_05_T
		self.Ano_05_06_H = Ano_05_06_H
		self.Ano_05_06_M = Ano_05_06_M
		self.Ano_05_06_T = Ano_05_06_T
		self.Ano_06_07_H = Ano_06_07_H
		self.Ano_06_07_M = Ano_06_07_M
		self.Ano_06_07_T = Ano_06_07_T
		self.Ano_07_08_H = Ano_07_08_H
		self.Ano_07_08_M = Ano_07_08_M
		self.Ano_07_08_T = Ano_07_08_T
		self.Ano_08_09_H = Ano_08_09_H
		self.Ano_08_09_M = Ano_08_09_M
		self.Ano_08_09_T = Ano_08_09_T
		self.Ano_09_10_H = Ano_09_10_H
		self.Ano_09_10_M = Ano_09_10_M
		self.Ano_09_10_T = Ano_09_10_T
		self.Ano_10_11_H = Ano_10_11_H
		self.Ano_10_11_M = Ano_10_11_M
		self.Ano_10_11_T = Ano_10_11_T

class BaseDados:
	'''
	Classe BaseDeDados
		- Nesta classe será feita a criação da mesma e posteriormente
		o seu preenchimento
	'''

	part_mem = memoria_partilhada()

	def verificar_base_dados(self):
		'''
		Metodo para verifica se a base de dados existe

		Returns:
			Devolve um boleano indicativo da existencia ou não
			da base de dados

		Raises:
			IOError: Base de dados não existe
		'''

		# excepção para verificar se já existe base de dados criada
		try:
			open('lp_python_1213.db')
		
		except IOError:
			#caso não exista a base de dados o metodo retorna falso
			return False	
			pass

		else:
			#caso a base de dados esteja presente devolve true
			return True
			pass


	def criar_base_dados(self):
		'''
		Metodo para a criação ba base de dados
		'''	
		#engine que vai trabalhar com a base de dados
		#esta variavel foi deixada em inglês propositadamente
		engine = create_engine('sqlite:///lp_python_1213.db', echo=False)
		
		metadata = Base.metadata
		metadata.create_all(engine)

		pass
	
	
	def tratamento_dados_xls(self, linha_xls, estabelecimento,
								unidade_organica, nivel_formacao):
		'''
		Metodo para o tratamento dos dados do ficheiro xls

		Args:
			linha_xls: linha do ficheiro excel

			estabelecimento: variavel "global" que guarda o estabelecimento de ensino 

			unidade_organica: variavel "global" que guarda a unidade organica 

			nivel_formacao: variavel "global" que guarda o nivel de formacao
			
		Returns:
			Devolve array com os dados uteis 
		'''
		
		estabelecimento_ciclo = estabelecimento
		unidade_organica_ciclo = unidade_organica
		nivel_formacao_ciclo = nivel_formacao
		
		#array que vai guardar os dados depois do tratamento
		linha_bd = []
		
		#variaveis utilizadas para guardar os dados até serem tratados
		estabelecimento_temp, unidade_organica_temp = None, None
		nivel_formacao_temp = None
		
		#Inicio do tratamento dos dados antes do envio para a BD			
		estabelecimento_temp = linha_xls[0].value.encode('utf-8')
		unidade_organica_temp = linha_xls[1].value.encode('utf-8')
		nivel_formacao_temp = linha_xls[2].value.encode('utf-8')
		curso_superior = linha_xls[3].value.encode('utf-8')
		area_formacao = linha_xls[4].value.encode('utf-8')
		
		# Ciclos para a verificação de espaços em branco no ficheiro
		# e substituição pelos valores correctos
		if estabelecimento_temp == '':
			pass
		else:
			estabelecimento_ciclo = estabelecimento_temp
			pass
		
		if unidade_organica_temp == '':
			pass
		else:			
			unidade_organica_ciclo = unidade_organica_temp
			pass
			
		if nivel_formacao_temp == '':
			pass
		else:
			nivel_formacao_ciclo = nivel_formacao_temp
			pass

		if estabelecimento_temp != '' and unidade_organica_temp == '':
			unidade_organica_ciclo = 'Nao Definida'
			pass
		
		#inserir os campos STRING no array

		linha_bd.append(estabelecimento_ciclo)		
		linha_bd.append(unidade_organica_ciclo)		
		linha_bd.append(nivel_formacao_ciclo)
		linha_bd.append(curso_superior)
		linha_bd.append(area_formacao)  
		
		for x in range(5, 54): # ciclo dos dados numericos
			
			# salta coluna sem valor	
			if x == 44: 
				pass

			# se o valor for '-', troca por 0
			elif linha_xls[x].value == '-':
				linha_bd.append(0)
				pass
			
			# converte valores das celulas(floats) para inteiros
			else:
				# converte o valor para string
				valor= str(linha_xls[x].value)
				
				# separa a string numa lista
				valor_split = valor.split('.') 
				
				# converte a primeira posicao para inteiro
				valor_inteiro = (int(valor_split[0]))
				linha_bd.append(valor_inteiro)
				pass
			
			pass
			
		# array criado para evitar o acesso directo às variaveis do
		# metodo preencher_base_dados
		dados_uteis = []
		dados_uteis.append(linha_bd)
		dados_uteis.append(estabelecimento_ciclo)
		dados_uteis.append(unidade_organica_ciclo)
		dados_uteis.append(nivel_formacao_ciclo)
			
		return dados_uteis

	def verificar_ficheiro_xls(self):
		'''
		Metodo para verificar a existencia do ficheiro xls

		Returns:
			Devolve o nome do ficheiro caso este esteja na directoria
			Se não estiver devolve um boleano falso

		Raises:
			IOError: Não existe ficheiro. O ficheiro deve ser colocado na pasta
		'''
		#guarda o nome do ficheiro xls que está na directoria do código
		nome_ficheiro = "Inscritos_2010-2011.xls"
		
		# excepção para verificar se o ficheiro existe na diretoria
		try:
			ficheiro_excel = open_workbook(nome_ficheiro)
			return nome_ficheiro
			pass
			
		except IOError:
			return False
			

	def preencher_base_dados(self, engine, nome_ficheiro):
		'''
		Metodo para o preenchimento da base de dados

		Args:
			engine: objecto que faz a comunicação com a base de dados
			nome_ficheiro: nome do ficheiro xls a carregar

		'''
		part_mem = memoria_partilhada()

		#abertura do ficheiro xls		
		ficheiro_excel = open_workbook(nome_ficheiro)

		#O session é o responsavel pelas transacções
		#mais uma vez o nome foi mantido em inglês
		Session = sessionmaker(bind=engine)
		session = Session()
		
		#O numero 30 é o index da folha onde constam os dados desejados
		folha_exel = ficheiro_excel.sheet_by_index(30)
		
		#estas variaveis são usadas para guardar os dados finais a serem
		#enviados para o array linhaDB que posteriormente é usado para
		#enviar os dados para a BD		
		estabelecimento, unidade_organica = None, None
		nivel_formacao = None
		
		# ciclo para leitura de cada linha 
		# não foi utilizado um metodo iterativo por ser mais
		# comodo para o tratamento dos dados
		for numero_linha in xrange(4, 11956): 

			# array com os dados de cada linha da folha do xls
			linha_xls = folha_exel.row(numero_linha)
			
			# array que vai guardar os dados do ficheiro
			# .xls depois do tratamento necessário
			dados_uteis = self.tratamento_dados_xls(linha_xls,
											estabelecimento,
											unidade_organica,
											nivel_formacao)
											
			linha_bd = dados_uteis[0]
			estabelecimento = dados_uteis[1]
			unidade_organica = dados_uteis[2]
			nivel_formacao = dados_uteis[3]
			
			#criação de um objecto da classe Registo
			objecto = Registo(linha_bd[0].decode('utf-8'),
				linha_bd[1].decode('utf-8'), linha_bd[2].decode('utf-8'),
				linha_bd[3].decode('utf-8'), linha_bd[4].decode('utf-8'),
				linha_bd[5], linha_bd[6], linha_bd[7], linha_bd[8],
				linha_bd[9], linha_bd[10], linha_bd[11], linha_bd[12],
				linha_bd[13], linha_bd[14], linha_bd[15], linha_bd[16],
				linha_bd[17], linha_bd[18], linha_bd[19], linha_bd[20],
				linha_bd[21], linha_bd[22], linha_bd[23], linha_bd[24],
				linha_bd[25], linha_bd[26], linha_bd[27], linha_bd[28],
				linha_bd[29], linha_bd[30], linha_bd[31], linha_bd[32],
				linha_bd[33], linha_bd[34], linha_bd[35], linha_bd[36],
				linha_bd[37], linha_bd[38], linha_bd[39], linha_bd[40],
				linha_bd[41], linha_bd[42], linha_bd[43], linha_bd[44],
				linha_bd[45], linha_bd[46], linha_bd[47], linha_bd[48],
				linha_bd[49], linha_bd[50], linha_bd[51], linha_bd[52])
			
			# adiciona o registo ao Session						   
			session.add(objecto)
			part_mem.set_status(numero_linha)
			# elimina o objecto, linha_bd e linha_xls
			del objecto, linha_bd, linha_xls, dados_uteis
			
			pass # fim do ciclo para leitura de cada linha

		# envia todo o conteudo do Session para a Base de Dados
		session.commit() 
		
		#Elimina a referencia ao ficheiro e folha
		del ficheiro_excel, folha_exel		
		pass

	def seleccionar_dados(self, engine):
		'''
		Metodo para o carregar da base de dados para uma lista

		Args:
			engine: objecto que faz a comunicação com a base de dados

		Returns:
			Devolve uma lista com o conteudo da base de dados

		'''

		part_mem = memoria_partilhada()

		Session = sessionmaker(bind=engine)
		session = Session()

		todos_os_dados_objectos = session.query(Registo)
		
		todos_os_dados = []

		numero_linha = 4

		for objecto in todos_os_dados_objectos:
			tuplo = (objecto.estabelecimento, objecto.unidade_organica,
			objecto.nivel_de_formacao, objecto.curso_superior,
			objecto.area_de_formacao, objecto.Ano_95_96_H,
			objecto.Ano_95_96_M, objecto.Ano_95_96_T,
			objecto.Ano_96_97_H, objecto.Ano_96_97_M,
			objecto.Ano_96_97_T, objecto.Ano_97_98_H,
			objecto.Ano_97_98_M, objecto.Ano_97_98_T,
			objecto.Ano_98_99_H, objecto.Ano_98_99_M,
			objecto.Ano_98_99_T, objecto.Ano_99_00_H,
			objecto.Ano_99_00_M, objecto.Ano_99_00_T,
			objecto.Ano_00_01_H, objecto.Ano_00_01_M,
			objecto.Ano_00_01_T, objecto.Ano_01_02_H,
			objecto.Ano_01_02_M, objecto.Ano_01_02_T,
			objecto.Ano_02_03_H, objecto.Ano_02_03_M,
			objecto.Ano_02_03_T, objecto.Ano_03_04_H,
			objecto.Ano_03_04_M, objecto.Ano_03_04_T,
			objecto.Ano_04_05_H, objecto.Ano_04_05_M,
			objecto.Ano_04_05_T, objecto.Ano_05_06_H,
			objecto.Ano_05_06_M, objecto.Ano_05_06_T,
			objecto.Ano_06_07_H, objecto.Ano_06_07_M,
			objecto.Ano_06_07_T, objecto.Ano_07_08_H,
			objecto.Ano_07_08_M, objecto.Ano_07_08_T,
			objecto.Ano_08_09_H, objecto.Ano_08_09_M,
			objecto.Ano_08_09_T, objecto.Ano_09_10_H,
			objecto.Ano_09_10_M, objecto.Ano_09_10_T,
			objecto.Ano_10_11_H, objecto.Ano_10_11_M,
			objecto.Ano_10_11_T)
			
			numero_linha += 1
			todos_os_dados.append(tuplo)
			part_mem.set_status(numero_linha)
			pass

		# devolve a lista com os dados da BD (lista de tuplos)
		return todos_os_dados